if exists (select 1
            from  sysobjects
           where  id = object_id('view_bd_group')
            and   type = 'V')
   drop view view_bd_group
go

/*==============================================================*/
/* View: view_bd_group                                          */
/*==============================================================*/
create view view_bd_group as
SELECT
	d1.dep_name AS deptName1,
	d1.dep_serial AS deptId1,
	d1.dep_no_add as deptCode1,
	d2.dep_name AS deptName2,
	d2.dep_serial AS deptId2,
	d2.dep_no_add as deptCode2,
	d3.dep_name AS deptName3,
	d3.dep_serial AS deptId3,
	d3.dep_no_add as deptCode3,
	d4.dep_name AS deptName4,
	d4.dep_serial AS deptId4,
	d4.dep_no_add as deptCode4,
	d5.dep_name AS deptName5,
	d5.dep_serial AS deptId5,
	d5.dep_no_add as deptCode5
FROM
	dt_dep d5
	LEFT JOIN dt_dep d4 ON d4.dep_serial= d5.dep_parent
	LEFT JOIN dt_dep d3 ON d3.dep_serial= d4.dep_parent
	LEFT JOIN dt_dep d2 ON d2.dep_serial= d3.dep_parent
	LEFT JOIN dt_dep d1 ON d1.dep_serial= d2.dep_parent 
WHERE
	d5.dep_serial NOT IN (
	SELECT DISTINCT
		dep_parent 
	FROM
	dt_dep 
	)
go
